﻿using System;
using System.Data.Common;
using System.Data.SqlClient;
using com.Norland.SPMS.SystemFramework;
using com.Norland.SPMS.Common.Data;
using com.Norland.SPMS.Common;
using System.Data;



namespace com.Norland.SPMS.DataAccess
{
	/// <summary>
	/// MenuItemDA 的摘要描述。
	/// </summary>
	public class MenuItemDAO
	{
		//
		// The DataSetCommand object
		//
		private SqlDataAdapter dsCommand;
		private SqlCommand loadCommand;
		private SqlCommand insertCommand;
		private SqlCommand updateCommand;
		private SqlCommand deleteCommand;
		SqlConnection conn;
	        
		/// <summary>
		///     Constructor for MenuItemDAO.  
		///     <remarks>Initialize the internal DataSetCommand object.</remarks> 
		/// </summary>
		public MenuItemDAO()
		{
			//
			// Create the adapter
			//
			dsCommand = new SqlDataAdapter();
			conn=new SqlConnection(SPMSConfiguration.DbConnectionString);
			//
			// Establish the mapping rules from the database columns to the dataset
			//
			// NOTE: Having the mappings allows the adapter to create the table and its
			//       columns directly in the dataset.
			// 
			dsCommand.TableMappings.Add("Table", MenuData.MENUITEMS_TABLE);
		}

		/// <summary>
		///     Dispose of this object's resources.
		/// </summary>
		public void Dispose()
		{
			Dispose(true);
			GC.SuppressFinalize(true); // as a service to those who might inherit from us
		}

		/// <summary>
		///		Free the instance variables of this object.
		/// </summary>
		protected virtual void Dispose(bool disposing)
		{
			if (! disposing)
				return; // we're being collected, so let the GC take care of this object

			if (dsCommand != null )
			{
				if (dsCommand.SelectCommand != null)
				{
					if( dsCommand.SelectCommand.Connection != null)
						dsCommand.SelectCommand.Connection.Dispose();
					dsCommand.SelectCommand.Dispose();
				}
				dsCommand.Dispose();
				dsCommand = null;
			}
		}
		private MenuData FillMenuData(String commandText, String paramName, String paramValue)
		{
			if (dsCommand == null )
			{
				throw new System.ObjectDisposedException( GetType().FullName );
			}            
			MenuData   data    = new MenuData();
			//dsCommand.SelectCommand
			SqlCommand command = dsCommand.SelectCommand;

			command.CommandText = commandText;
			command.CommandType = CommandType.StoredProcedure; // use stored proc for perf
			SqlParameter param = new SqlParameter(paramName, SqlDbType.NVarChar, 255);
			param.Value = paramValue;
			command.Parameters.Add(param);            

			dsCommand.Fill(data);
			return data;
		}

		public MenuData getAllMenuItems()//取得所有menuitem
		{
			if (dsCommand == null )
			{
				throw new System.ObjectDisposedException( GetType().FullName );
			}            
			MenuData   data    = new MenuData();
			dsCommand.SelectCommand=GetLoadCommand();
			dsCommand.Fill(data);
			return data;		
		}
		public int InsertMenuItems(MenuData md)
		{
		
			if ( dsCommand == null )
			{
				throw new System.ObjectDisposedException( GetType().FullName );
			}            
            
			dsCommand.InsertCommand = GetInsertCommand();
			int i = 0;
			try
			{
				i= dsCommand.Update(md, MenuData.MENUITEMS_TABLE);
			}
			catch(Exception eee)
			{
				Console.WriteLine(eee.Message);
			

			}
			//
			// Check for table errors to see if the update failed.
			//
			if ( md.HasErrors )
			{
				md.Tables[MenuData.MENUITEMS_TABLE].GetErrors()[0].ClearErrors();
				return -1;
			}
			else
			{
				md.AcceptChanges();
				return i;
			}

		}
		public int DeleteMenuItems(MenuData md)
		{
			if ( dsCommand == null )
			{
				throw new System.ObjectDisposedException( GetType().FullName );
			}            
            
			dsCommand.DeleteCommand = GetDeleteCommand();
            
			int i= dsCommand.Update(md, MenuData.MENUITEMS_TABLE);
			//
			// Check for table errors to see if the update failed.
			//
			if ( md.HasErrors )
			{
				md.Tables[MenuData.MENUITEMS_TABLE].GetErrors()[0].ClearErrors();
				return -1;
			}
			else
			{
				md.AcceptChanges();
				return i;
			}

		
		}



		public int UpdateMenuItems(MenuData md)
		{
			if ( dsCommand == null )
			{
				throw new System.ObjectDisposedException( GetType().FullName );
			}        
			dsCommand.UpdateCommand=GetUpdateCommand();
			int i= dsCommand.Update(md, MenuData.MENUITEMS_TABLE);
			//
			// Check for table errors to see if the update failed.
			//
			if ( md.HasErrors )
			{
				md.Tables[MenuData.MENUITEMS_TABLE].GetErrors()[0].ClearErrors();
				return -1;
			}
			else
			{
				md.AcceptChanges();
				return i;
			}

		}



		private SqlCommand GetInsertCommand()
		{
			if ( insertCommand == null )
			{
				insertCommand = new SqlCommand();
				insertCommand.Connection=conn;
				
				insertCommand.CommandText = @"INSERT INTO MENUITEMS( CAPTION, PARENT, PACKAGE, FORM, ISMDIFORM, SEQ_NO, REMARK) VALUES ( @CAPTION, @PARENT, @PACKAGE, @FORM, @ISMDIFORM, @SEQ_NO, @REMARK)"; 
			
				//insertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@MENUID", System.Data.SqlDbType.VarChar, 25, "MENUID"));
				insertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CAPTION", System.Data.SqlDbType.NVarChar, 50, "CAPTION"));
				insertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PARENT", System.Data.SqlDbType.Int, 4, "PARENT"));
				insertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PACKAGE", System.Data.SqlDbType.VarChar, 500, "PACKAGE"));
				insertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@FORM", System.Data.SqlDbType.VarChar, 500, "FORM"));
				insertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ISMDIFORM", System.Data.SqlDbType.VarChar, 1, "ISMDIFORM"));
				insertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@SEQ_NO", System.Data.SqlDbType.VarChar, 4, "SEQ_NO"));
				insertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@REMARK", System.Data.SqlDbType.NVarChar, 500, "REMARK"));

			}
			return(insertCommand);
		}

		private SqlCommand GetLoadCommand()
		{
			if ( loadCommand == null )
			{
				//
				// Construct the command since we don't have it already
				//
				loadCommand = new SqlCommand();
				loadCommand.Connection=conn;
				loadCommand.CommandText="select * from "+MenuData.MENUITEMS_TABLE;

			}
            
			return loadCommand;
		}
		private SqlCommand GetDeleteCommand()
		{
			if ( deleteCommand == null )
			{
				//
				// Construct the command since we don't have it already
				//
				deleteCommand = new SqlCommand();
				deleteCommand.Connection=conn;
				
				this.deleteCommand.CommandText = @"DELETE FROM MENUITEMS WHERE (MENUID = @Original_MENUID) AND (CAPTION = @Original_CAPTION) AND (FORM = @Original_FORM OR @Original_FORM IS NULL AND FORM IS NULL)  AND (ISMDIFORM = @Original_ISMDIFORM OR @Original_ISMDIFORM IS NULL AND ISMDIFORM IS NULL)   AND (PACKAGE = @Original_PACKAGE OR @Original_PACKAGE IS NULL AND PACKAGE IS NULL)  AND (PARENT = @Original_PARENT OR @Original_PARENT IS NULL AND PARENT IS NULL) AND (SEQ_NO = @Original_SEQ_NO OR @Original_SEQ_NO IS NULL AND SEQ_NO IS NULL) AND (REMARK = @Original_REMARK OR @Original_REMARK IS NULL AND REMARK IS NULL)";

				this.deleteCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_MENUID", System.Data.SqlDbType.VarChar, 25, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "MENUID", System.Data.DataRowVersion.Original, null));
				this.deleteCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_CAPTION", System.Data.SqlDbType.VarChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "CAPTION", System.Data.DataRowVersion.Original, null));
				this.deleteCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_FORM", System.Data.SqlDbType.VarChar, 500, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "FORM", System.Data.DataRowVersion.Original, null));
				this.deleteCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_ISMDIFORM", System.Data.SqlDbType.VarChar, 1, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "ISMDIFORM", System.Data.DataRowVersion.Original, null));
				this.deleteCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_PACKAGE", System.Data.SqlDbType.VarChar, 500, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "PACKAGE", System.Data.DataRowVersion.Original, null));
				this.deleteCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_PARENT", System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "PARENT", System.Data.DataRowVersion.Original, null));
				this.deleteCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_SEQ_NO", System.Data.SqlDbType.VarChar, 4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "SEQ_NO", System.Data.DataRowVersion.Original, null));
				this.deleteCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_REMARK", System.Data.SqlDbType.NVarChar, 500, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "REMARK", System.Data.DataRowVersion.Original, null));

				// 


			}
            
			return deleteCommand;
		}

		private SqlCommand GetUpdateCommand()
		{
			if ( loadCommand == null )
			{
				//
				// Construct the command since we don't have it already
				//
				updateCommand = new SqlCommand();
				updateCommand.Connection=conn;
				
				this.updateCommand.CommandText = @"UPDATE MENUITEMS SET MENUID = @MENUID, CAPTION = @CAPTION, PARENT = @PARENT, PACKAGE = @PACKAGE, FORM = @FORM, ISMDIFORM = @ISMDIFORM,  SEQ_NO = @SEQ_NO, REMARK = @REMARK WHERE MENUID = @Original_MENUID ";

				this.updateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@MENUID", System.Data.SqlDbType.VarChar, 25, "MENUID"));
				this.updateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CAPTION", System.Data.SqlDbType.NVarChar, 50, "CAPTION"));
				this.updateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PARENT", System.Data.SqlDbType.VarChar, 20, "PARENT"));
				this.updateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PACKAGE", System.Data.SqlDbType.VarChar, 500, "PACKAGE"));
				this.updateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@FORM", System.Data.SqlDbType.VarChar, 500, "FORM"));
				this.updateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ISMDIFORM", System.Data.SqlDbType.VarChar, 1, "ISMDIFORM"));
				this.updateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@SEQ_NO", System.Data.SqlDbType.VarChar, 4, "SEQ_NO"));
                this.updateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@REMARK", System.Data.SqlDbType.NVarChar, 500, "REMARK"));

				this.updateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_MENUID", System.Data.SqlDbType.VarChar, 25, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "MENUID", System.Data.DataRowVersion.Original, null));


			}
            
			return updateCommand;
		}


	}//class MenuItemDAO
}
